/*
 Navicat Premium Data Transfer

 Source Server         : demo
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : localhost:3306
 Source Schema         : 0309-monthTest

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 08/06/2023 15:53:15
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of category
-- ----------------------------
BEGIN;
INSERT INTO `category` (`id`, `name`) VALUES (1, '类别1');
INSERT INTO `category` (`id`, `name`) VALUES (2, '类别2');
INSERT INTO `category` (`id`, `name`) VALUES (3, '类别3');
COMMIT;

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of grade
-- ----------------------------
BEGIN;
INSERT INTO `grade` (`id`, `name`) VALUES (1, 'zhiji1');
INSERT INTO `grade` (`id`, `name`) VALUES (2, 'zhiji2');
INSERT INTO `grade` (`id`, `name`) VALUES (3, 'zhiji3');
INSERT INTO `grade` (`id`, `name`) VALUES (4, 'zhiji4');
INSERT INTO `grade` (`id`, `name`) VALUES (5, 'zhiji5');
COMMIT;

-- ----------------------------
-- Table structure for job_info
-- ----------------------------
DROP TABLE IF EXISTS `job_info`;
CREATE TABLE `job_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  `organization_id` int NOT NULL,
  `position_id` int NOT NULL,
  `category_id` int NOT NULL,
  `grade_id` int NOT NULL,
  `status` int NOT NULL,
  `desc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_aaaa` (`organization_id`,`position_id`,`category_id`,`grade_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of job_info
-- ----------------------------
BEGIN;
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (1, 'java工程师', '1213112', 1, 1, 1, 1, 1, '山东分公司如果温柔色');
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (2, 'pthon工程师', '1213112', 2, 1, 2, 3, 2, '山东分公司如果温柔色');
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (3, 'c工程师', '1213112', 3, 2, 1, 1, 2, '山东分公司如果温柔色');
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (4, 'ios工程师', '1213112', 3, 2, 1, 2, 2, '山东分公司如果温柔色');
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (5, 'android工程师', '1213112', 4, 2, 2, 2, 2, '山东分公司如果温柔色');
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (6, 'c++工程师', '1213112', 4, 2, 2, 2, 2, '山东分公司如果温柔色');
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (7, 'c#工程师', '1213112', 4, 2, 3, 1, 2, '山东分公司如果温柔色');
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (8, NULL, NULL, 2, 1, 1, 5, 1, NULL);
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (9, NULL, NULL, 4, 1, 1, 1, 1, NULL);
INSERT INTO `job_info` (`id`, `name`, `code`, `organization_id`, `position_id`, `category_id`, `grade_id`, `status`, `desc`) VALUES (10, NULL, NULL, 2, 1, 1, 1, 1, NULL);
COMMIT;

-- ----------------------------
-- Table structure for organization
-- ----------------------------
DROP TABLE IF EXISTS `organization`;
CREATE TABLE `organization` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of organization
-- ----------------------------
BEGIN;
INSERT INTO `organization` (`id`, `name`) VALUES (1, '市场部');
INSERT INTO `organization` (`id`, `name`) VALUES (2, '运营部');
INSERT INTO `organization` (`id`, `name`) VALUES (3, '产品部');
INSERT INTO `organization` (`id`, `name`) VALUES (4, '技术部');
COMMIT;

-- ----------------------------
-- Table structure for position
-- ----------------------------
DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of position
-- ----------------------------
BEGIN;
INSERT INTO `position` (`id`, `name`) VALUES (1, '职务1');
INSERT INTO `position` (`id`, `name`) VALUES (2, '职务2');
INSERT INTO `position` (`id`, `name`) VALUES (3, '职务3');
INSERT INTO `position` (`id`, `name`) VALUES (4, '职务4');
COMMIT;

-- ----------------------------
-- Procedure structure for zxt
-- ----------------------------
DROP PROCEDURE IF EXISTS `zxt`;
delimiter ;;
CREATE PROCEDURE `zxt`()
BEGIN
SELECT
	count(*) AS ydata,
	organization.NAME AS xdata 
FROM
	job_info
	LEFT JOIN organization ON job_info.organization_id = organization.id
	
GROUP BY
	job_info.organization_id;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for zxtByParam
-- ----------------------------
DROP PROCEDURE IF EXISTS `zxtByParam`;
delimiter ;;
CREATE PROCEDURE `zxtByParam`(IN zhiji int, IN miaoshu varchar(50))
BEGIN

	SELECT
	count(*) AS ydata,
	organization.NAME AS xdata 
FROM
	job_info
	LEFT JOIN organization ON job_info.organization_id = organization.id
	 Where job_info.grade_id  = zhiji Or  job_info.`desc` = miaoshu
GROUP BY
	job_info.organization_id;

END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
